import random

f = open('file.sql', mode='a', encoding='utf8')

del_sql = f'DELETE FROM RFN_STOCK_PAYBACK_NOTIFY;\nDELETE FROM RFN_FUND_PAYBACK_NOTIFY;\n'
f.writelines(del_sql)
_count = 1

while _count <= 50:
    radint = int(random.random() * 1000000)
    if len(str(radint)) == 6:
        # print(radint)
        _count += 1
        # 上海stock和fund记录
        bond_no_s1 = f'2022071301{radint}'
        contranct_no_s1 = f'0001{radint}'
        bond_no_f1 = f'2022071301{radint}'
        contranct_no_f1 = f'0001{radint}'
        # 深圳stock和fund记录
        bond_no_s2 = f'2022071302{radint}'
        contranct_no_s2 = f'0002{radint}'
        bond_no_f2 = f'2022071302{radint}'
        contranct_no_f2 = f'0002{radint}'
        sql_spayback1 = f"INSERT INTO KS.RFN_STOCK_PAYBACK_NOTIFY (BRANCH_CODE, CUST_NO, OCCUR_DATE, CLR_DATE, DONE_DATE, BOND_NO, BOND_TYPE, CONTRACT_NO, MARKET_CODE, HOLDER_ACC, SEAT_NO, TERM_CODE, FEE_RATE, SEC_CODE, REF_VOL, REF_AMT, DIVISION_COMPENSATION, PROSPECTIVE_FEE, EXPIRY_DATE, PAYBACK_VOL, PAYBACK_STOCK_AMT, PAYBACK_COMPENSATION, PAYBACK_INTEREST, PAYBACK_FINE, PAYBACK_PENALTY, PAYBACK_OTHER_FEE, PAYBACKING_VOL, PAYBACKING_STOCK_AMT, PAYBACKING_COMPENSATION, PAYBACKING_INTEREST, PAYBACKING_FINE, PAYBACKING_PENALTY, PAYBACKING_OTHER_FEE, PAYBACKED_VOL, PAYBACKED_STOCK_AMT, PAYBACKED_COMPENSATION, PAYBACKED_INTEREST, PAYBACKED_FINE, PAYBACKED_PENALTY, PAYBACKED_OTHER_FEE, PAY_INTEREFACE, PAY_DATE, PAY_TIME, PAY_STOCK_INTEREFACE, PAY_STOCK_DATE, PAY_STOCK_TIME, LAST_TIMESTAMP, SETTLE_FLAG, NOTE, RESERVE_VOL, REDUCE_FLAG) VALUES ('000', '0000000015', INT(CURRENT DATE), INT(CURRENT DATE), INT(CURRENT DATE), '{bond_no_s1}', '0', '{contranct_no_s1}', '1', 'D890778722', '', 1, 1, '688011', 10000, 4000000, 0, 111.11, '20220714', 10000, 0, 0, 111.11, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '1', '', '', '1', '', '', '2022-07-13 20:20:08.786', '0', '', 0, '0');\n"
        sql_spayback2 = f"INSERT INTO KS.RFN_STOCK_PAYBACK_NOTIFY (BRANCH_CODE, CUST_NO, OCCUR_DATE, CLR_DATE, DONE_DATE, BOND_NO, BOND_TYPE, CONTRACT_NO, MARKET_CODE, HOLDER_ACC, SEAT_NO, TERM_CODE, FEE_RATE, SEC_CODE, REF_VOL, REF_AMT, DIVISION_COMPENSATION, PROSPECTIVE_FEE, EXPIRY_DATE, PAYBACK_VOL, PAYBACK_STOCK_AMT, PAYBACK_COMPENSATION, PAYBACK_INTEREST, PAYBACK_FINE, PAYBACK_PENALTY, PAYBACK_OTHER_FEE, PAYBACKING_VOL, PAYBACKING_STOCK_AMT, PAYBACKING_COMPENSATION, PAYBACKING_INTEREST, PAYBACKING_FINE, PAYBACKING_PENALTY, PAYBACKING_OTHER_FEE, PAYBACKED_VOL, PAYBACKED_STOCK_AMT, PAYBACKED_COMPENSATION, PAYBACKED_INTEREST, PAYBACKED_FINE, PAYBACKED_PENALTY, PAYBACKED_OTHER_FEE, PAY_INTEREFACE, PAY_DATE, PAY_TIME, PAY_STOCK_INTEREFACE, PAY_STOCK_DATE, PAY_STOCK_TIME, LAST_TIMESTAMP, SETTLE_FLAG, NOTE, RESERVE_VOL, REDUCE_FLAG) VALUES ('000', '0000000015', INT(CURRENT DATE), INT(CURRENT DATE), INT(CURRENT DATE), '{bond_no_s2}', '0', '{contranct_no_s2}', '2', '0689900003', '', 1, 1, '300001', 10000, 214400, 0, 5.96, '20220714', 10000, 0, 0, 5.96, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2', '', '', '2', '', '', '2022-07-13 20:20:08.786', '0', '', 0, '0');\n"
        sql_sdetail1 = f"INSERT INTO KS.RFN_STOCK_IN_DETAIL (BRANCH_CODE, CUST_NO, OCCUR_DATE, CLR_DATE, DONE_DATE, BOND_NO, BOND_TYPE, CONTRACT_NO, MARKET_CODE, HOLDER_ACC, SEAT_NO, TERM_CODE, FEE_RATE, SEC_CODE, SEC_PRICE, REF_VOL, REF_AMT, DIVISION_COMPENSATION, PROSPECTIVE_FEE, PROSPECTIVE_FINE, PROSPECTIVE_PENALTY, EXPIRY_DATE, OCCUR_VOL, OCCUR_INTEREST, OCCUR_OTHER_FEE, RECEIVE_OP_PENALTY, RECEIVE_OP_FINE, RECEIVE_DATE, DUE_PAYBACK_DATE, PAYBACK_VOL, PAYBACK_AMT, PAYBACK_COMPENSATION, PAYBACK_INTEREST, PAYBACK_OTHER_FEE, PAYBACK_DATE, DELAY_PAYBACK_VOL, DELAY_PAYBACK_AMT, DELAY_PAYBACK_COMPENSATION, DELAY_PAYBACK_INTEREST, DELAY_PAYBACK_FINE, DELAY_PAYBACK_PENALTY, DELAY_PAYBACK_OTHER_FEE, DELAY_PAYBACK_DATE, BOND_STATUS, TODAY_INTEREST, TODAY_FINE, TODAY_PENALTY, LAST_TIMESTAMP, SETTLE_FLAG, SOC_NO, CALL_BACK_DATE, CALL_BACK_REASON, AGREE_CALL_BACK_FLAG, RENEW_RPT_DATE, AGREE_RENEW_FLAG, NOTE, SUM_TERM, EXTENSION_NUM, ORDER_BUSINESS_CODE, DSF_BOND_NO, DSF_HOLDER_ACC) VALUES ('000', '0000000015', 20220610, 20220610, 20220610, '{bond_no_s1}', '0', '{contranct_no_s1}', '1', 'D890778722', '09X25', 3, 4, '688011', 4.19, 10000, 20950, 0, 6.98, 0, 0, '20220613', 10000, 0, 0, 0, 0, '', '20220613', 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, 0, '', '2', 121.08, 366.8, 0, '2022-06-10 22:18:54.505', '0', '', '', '', '', '', '', '', 3, 0, '0736', '2022061000000233', '');\n"
        sql_sdetail2 = f"INSERT INTO KS.RFN_STOCK_IN_DETAIL (BRANCH_CODE, CUST_NO, OCCUR_DATE, CLR_DATE, DONE_DATE, BOND_NO, BOND_TYPE, CONTRACT_NO, MARKET_CODE, HOLDER_ACC, SEAT_NO, TERM_CODE, FEE_RATE, SEC_CODE, SEC_PRICE, REF_VOL, REF_AMT, DIVISION_COMPENSATION, PROSPECTIVE_FEE, PROSPECTIVE_FINE, PROSPECTIVE_PENALTY, EXPIRY_DATE, OCCUR_VOL, OCCUR_INTEREST, OCCUR_OTHER_FEE, RECEIVE_OP_PENALTY, RECEIVE_OP_FINE, RECEIVE_DATE, DUE_PAYBACK_DATE, PAYBACK_VOL, PAYBACK_AMT, PAYBACK_COMPENSATION, PAYBACK_INTEREST, PAYBACK_OTHER_FEE, PAYBACK_DATE, DELAY_PAYBACK_VOL, DELAY_PAYBACK_AMT, DELAY_PAYBACK_COMPENSATION, DELAY_PAYBACK_INTEREST, DELAY_PAYBACK_FINE, DELAY_PAYBACK_PENALTY, DELAY_PAYBACK_OTHER_FEE, DELAY_PAYBACK_DATE, BOND_STATUS, TODAY_INTEREST, TODAY_FINE, TODAY_PENALTY, LAST_TIMESTAMP, SETTLE_FLAG, SOC_NO, CALL_BACK_DATE, CALL_BACK_REASON, AGREE_CALL_BACK_FLAG, RENEW_RPT_DATE, AGREE_RENEW_FLAG, NOTE, SUM_TERM, EXTENSION_NUM, ORDER_BUSINESS_CODE, DSF_BOND_NO, DSF_HOLDER_ACC) VALUES ('000', '0000000015', 20220610, 20220610, 20220610, '{bond_no_s2}', '0', '{contranct_no_s2}', '2', '0689900003', '016606', 7, 3.9, '300001', 15.79, 10000, 157900, 0, 205.28, 395.11, 0, '20220617', 10000, 0, 0, 0, 0, '', '20220617', 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, 0, '', '2', 889.64, 2447.45, 0, '2022-06-10 22:18:54.505', '0', '', '', '', '', '', '', '', 7, 0, '0736', '2022061000000239', '');\n"
        sql_fpayback1 = f"INSERT INTO KS.RFN_FUND_PAYBACK_NOTIFY (BRANCH_CODE, CUST_NO, OCCUR_DATE, CLR_DATE, DONE_DATE, BOND_NO, BOND_TYPE, CONTRACT_NO, MARKET_CODE, HOLDER_ACC, SEAT_NO, TERM_CODE, FEE_RATE, CURRENCY_TYPE, REF_AMT, PROSPECTIVE_FEE, EXPIRY_DATE, PAYBACK_AMT, PAYBACK_INTEREST, PAYBACK_FINE, PAYBACK_PENALTY, PAYBACK_OTHER_FEE, PAYBACKING_AMT, PAYBACKING_INTEREST, PAYBACKING_FINE, PAYBACKING_PENALTY, PAYBACKING_OTHER_FEE, PAYBACKED_AMT, PAYBACKED_INTEREST, PAYBACKED_FINE, PAYBACKED_PENALTY, PAYBACKED_OTHER_FEE, PAY_INTEREFACE, PAY_DATE, PAY_TIME, LAST_TIMESTAMP, NOTE) VALUES ('000', '0000002301', INT(CURRENT DATE), INT(CURRENT DATE), INT(CURRENT DATE), '{bond_no_f1}', '0', '{contranct_no_f1}', '1', 'D890778722', '', 1, 5.6, '01', 1000000, 155.56, INT(CURRENT DATE), 1000000, 155.56, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '1', '', '', '2022-03-31 21:27:07.139', '');\n"
        sql_fpayback2 = f"INSERT INTO KS.RFN_FUND_PAYBACK_NOTIFY (BRANCH_CODE, CUST_NO, OCCUR_DATE, CLR_DATE, DONE_DATE, BOND_NO, BOND_TYPE, CONTRACT_NO, MARKET_CODE, HOLDER_ACC, SEAT_NO, TERM_CODE, FEE_RATE, CURRENCY_TYPE, REF_AMT, PROSPECTIVE_FEE, EXPIRY_DATE, PAYBACK_AMT, PAYBACK_INTEREST, PAYBACK_FINE, PAYBACK_PENALTY, PAYBACK_OTHER_FEE, PAYBACKING_AMT, PAYBACKING_INTEREST, PAYBACKING_FINE, PAYBACKING_PENALTY, PAYBACKING_OTHER_FEE, PAYBACKED_AMT, PAYBACKED_INTEREST, PAYBACKED_FINE, PAYBACKED_PENALTY, PAYBACKED_OTHER_FEE, PAY_INTEREFACE, PAY_DATE, PAY_TIME, LAST_TIMESTAMP, NOTE) VALUES ('000', '0000002301', INT(CURRENT DATE), INT(CURRENT DATE), INT(CURRENT DATE), '{bond_no_f2}', '0', '{contranct_no_f2}', '2', '0689900003', '', 1, 5.6, '01', 1000000, 155.56, INT(CURRENT DATE), 1000000, 155.56, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2', '', '', '2022-03-31 21:27:07.139', '');\n"
        sql_fdetail1 = f"INSERT INTO KS.RFN_FUND_IN_DETAIL (BRANCH_CODE, CUST_NO, OCCUR_DATE, CLR_DATE, DONE_DATE, BOND_NO, BOND_TYPE, CONTRACT_NO, MARKET_CODE, HOLDER_ACC, SEAT_NO, TERM_CODE, FEE_RATE, CURRENCY_TYPE, REF_AMT, PROSPECTIVE_FEE, PROSPECTIVE_FINE, PROSPECTIVE_PENALTY, EXPIRY_DATE, OCCUR_AMT, OCCUR_INTEREST, OCCUR_OTHER_FEE, RECEIVE_OP_PENALTY, RECEIVE_OP_FINE, RECEIVE_DATE, PAYBACK_AMT, PAYBACK_INTEREST, PAYBACK_OTHER_FEE, DELAY_PAYBACK_AMT, DELAY_PAYBACK_INTEREST, DELAY_PAYBACK_FINE, DELAY_PAYBACK_PENALTY, DELAY_PAYBACK_OTHER_FEE, DELAY_PAYBACK_DATE, TODAY_INTEREST, TODAY_FINE, TODAY_PENALTY, DUE_PAYBACK_DATE, PAYBACK_DATE, BOND_STATUS, LAST_TIMESTAMP, SETTLE_FLAG, SOC_NO, RENEW_RPT_DATE, AGREE_RENEW_FLAG, NOTE) VALUES ('000', '0000002301', 20220314, 20220314, 20220314, '{bond_no_f1}', '0', '{contranct_no_f1}', '1', 'D890778722', '09X25', 1, 5.6, '01', 1000000, 155.56, 0, 0, INT(CURRENT DATE), 1000000, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, 0, 0, '', 19911.46, 42500, 0, '20220315', '', '2', '2022-03-30 13:32:09.187', '0', '', '', '', '');\n"
        sql_fdetail2 = f"INSERT INTO KS.RFN_FUND_IN_DETAIL (BRANCH_CODE, CUST_NO, OCCUR_DATE, CLR_DATE, DONE_DATE, BOND_NO, BOND_TYPE, CONTRACT_NO, MARKET_CODE, HOLDER_ACC, SEAT_NO, TERM_CODE, FEE_RATE, CURRENCY_TYPE, REF_AMT, PROSPECTIVE_FEE, PROSPECTIVE_FINE, PROSPECTIVE_PENALTY, EXPIRY_DATE, OCCUR_AMT, OCCUR_INTEREST, OCCUR_OTHER_FEE, RECEIVE_OP_PENALTY, RECEIVE_OP_FINE, RECEIVE_DATE, PAYBACK_AMT, PAYBACK_INTEREST, PAYBACK_OTHER_FEE, DELAY_PAYBACK_AMT, DELAY_PAYBACK_INTEREST, DELAY_PAYBACK_FINE, DELAY_PAYBACK_PENALTY, DELAY_PAYBACK_OTHER_FEE, DELAY_PAYBACK_DATE, TODAY_INTEREST, TODAY_FINE, TODAY_PENALTY, DUE_PAYBACK_DATE, PAYBACK_DATE, BOND_STATUS, LAST_TIMESTAMP, SETTLE_FLAG, SOC_NO, RENEW_RPT_DATE, AGREE_RENEW_FLAG, NOTE) VALUES ('000', '0000002301', 20220314, 20220314, 20220314, '{bond_no_f2}', '0', '{contranct_no_f2}', '2', '0689900003', '016606', 1, 5.6, '01', 1000000, 155.56, 0, 0, INT(CURRENT DATE), 1000000, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, 0, 0, '', 19911.46, 42500, 0, '20220315', '', '2', '2022-03-30 13:32:09.187', '0', '', '', '', '');\n"
        sql_lines = [sql_spayback1, sql_spayback2, sql_sdetail1, sql_sdetail2, sql_fpayback1, sql_fpayback2,
                     sql_fdetail1, sql_fdetail2]
        f.writelines(sql_lines)
